package cn.com.longfuinfo.wx.service;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.aop.Before;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.longfuinfo.wx.common.OrderCode;
import cn.com.longfuinfo.wx.dao.Recharge;
import cn.com.longfuinfo.wx.dao.User;
import cn.com.longfuinfo.wx.dao.UserCollection;
import cn.com.longfuinfo.wx.dao.UserMess;
import cn.com.longfuinfo.wx.dao.UserMoney;
import cn.com.longfuinfo.wx.dao.WxUser;
import cn.com.longfuinfo.wx.dao.AdSeller;
import cn.com.longfuinfo.wx.dao.HomeBag;
import cn.com.longfuinfo.wx.kit.DatetimeKit;

public class UserService {
	/**
	 * 查询指定用户名的用户信息，用于登录密码验证或验证指定用户名用户是否存在，所以只查询用户名和密码字段
	 */
//	public User findByPhone(String phone) {
//		return User.me().findFirst("SELECT * FROM t_bus_user WHERE phone=?", Phone);
//	}
	/**
	 * 查询指定用户ID的用户信息
	 */
	public User findByUser(int id) {
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE id=?", id);
	}
	/**
	 * 查询指定用户ID的用户信息
	 */
	public WxUser findByWxUser(int id) {
		return WxUser.me().findFirst("SELECT * FROM t_bus_user_wx WHERE id=?", id);
	}
	/**
	 * 根据微信ID查询指定用户ID的用户信息
	 */
	public User findByUserid(int userid) {
		return User.me().findFirst("SELECT A.*,B.nickname,B.openid FROM t_bus_user A LEFT JOIN  t_bus_user_wx B on A.id=B.userid WHERE B.id=?", userid);
	}

	/**
	 * 通过openid查询用户
	 */
	public WxUser findByOpenid(String openid) {
		return WxUser.me().findFirst("SELECT * FROM t_bus_user_wx WHERE openid=?", openid);
	}
	 
	/**
	 * 统计用户
	 * 
	 */
	public Record getUserState2(int userid) {
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		List<Object> sqlParams = new ArrayList<Object>();
		// 1.统计用户收藏的商品数量
		sqlBuffer.append(
				"(SELECT COUNT(*) FROM t_bus_user_mess buc WHERE buc.userid=? AND buc.state=1) mess ");
		sqlParams.add(userid);
		return Db.findFirst(sqlBuffer.toString(), sqlParams.toArray());
	}
	/**
	 * 统计用户各状态数量 1.统计用户收藏的商品数量 2.统计查询“待付款”的订单数量 3.统计查询“待发货”的订单数量
	 * 4.统计查询“待签收”的订单数量
	 */
	public Record getUserState(int userid) {
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		List<Object> sqlParams = new ArrayList<Object>();
		// 1.统计用户收藏的商品数量
		sqlBuffer.append(
				"(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.userid=? AND buc.isCancel=0) state1,");
		sqlParams.add(userid);

		// 2.统计“待付款”的商品订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND payState=? AND delState!=?) state2,");
		sqlParams.add(userid);
		sqlParams.add(OrderCode.OrderPayState.NO_PAY);
		sqlParams.add(OrderCode.DelState.YES);
		// 3.统计查询“待兑换”的订单数量
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=?");
		sqlBuffer.append(" AND payState=? AND orderState=? AND delState!=? and tuistate=?) state3,");
		sqlParams.add(userid);
		sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderState.NO_CON);
		sqlParams.add(OrderCode.DelState.YES);
		sqlParams.add(OrderCode.RefundState.ZERO);
		// 4.统计查询“已兑换”的订单数量
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND payState=? AND orderState=? AND delState!=?) state4,");
		sqlParams.add(userid);
		sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderState.had_CON);
		sqlParams.add(OrderCode.DelState.YES);
		// 4.统计查询“已过期”的订单数量
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND payState=? AND orderState=? AND delState!=? and tuistate!=?) state5 ");
		sqlParams.add(userid);
		sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderState.EXPIRE);
		sqlParams.add(OrderCode.DelState.YES);
		sqlParams.add(OrderCode.RefundState.REFUNDED);		
		return Db.findFirst(sqlBuffer.toString(), sqlParams.toArray());
	}

	/**
	 * 用户收藏指定商品\n
	 * 因为收藏表中记录为了统计，所以用户取消收藏是不会将记录删除的，只是修改了标识isCancel为1\n
	 * 所以在此业务中需要判断同一用户同一商品是否已经存在，如果存在且是取消收藏状态，则只需要修改标识为0即可\n
	 * 如果不存在，则需要新插入记录\n
	 * 否则认为同一用户已经收藏过同一商品，直接返回true
	 */
	@Before(Tx.class)
	public UserCollection doCollect(int userid, String goodsid) {
		UserCollection c = UserCollection.me()
				.findFirst("SELECT * FROM t_bus_user_collection WHERE userid=? AND goodsid=?", userid, goodsid);
		if (c != null) {
			if (c.getInt("isCancel") == 0) {
				c.set("isCancel", 1).update();
			} else {
				c.set("isCancel", 0).update();
			}
		} else {
			c = new UserCollection();
			c.set("userid", userid).set("goodsid", goodsid).save();
		}
		return c;
	}

	/**
	 * 用户取消收藏指定商品\n
	 */
	public void cancelCollect(int userid, String[] goodsids) {
		String sql = "UPDATE t_bus_user_collection SET isCancel=1 WHERE userid=? AND goodsid=?";
		Object[][] params = new Object[goodsids.length][2];
		for (int index = 0; index < goodsids.length; index++) {
			params[index][0] = userid;
			params[index][1] = goodsids[index];
		}
		Db.batch(sql, params, goodsids.length);
	}

	/**
	 * 查询用户收藏商品列表
	 * 
	 * @param storeid
	 *            店铺ID
	 * @param categoryCode
	 *            主分类代码
	 * @param type
	 *            优惠类型
	 * @param pageNum
	 *            页码
	 * @param pageSize
	 *            数量
	 */
	public Page<Record> findCollectionByPage(int userid, int pageNum, int pageSize) {
		String sqlSelect = "SELECT coled.* ";
		String sqlExceptSelect = "FROM (SELECT bg.id, bg.`name`,bg.type,cast(bg.sellPrice as char(11)) sellPrice,cast(bg.marketPrice as char(11)) marketPrice,bg.paytype,bg.sellpoint,bg.thumb,uc.createDate collectDate ,IFNULL(sss.countnum,0) as num "
				+ "FROM t_bus_user_collection uc  INNER JOIN t_bus_goods bg ON uc.goodsid=bg.id LEFT JOIN (select goodsid ,SUM(countnum) as countnum  from t_bus_order where payState=2 GROUP BY goodsid) sss on sss.goodsid=bg.id "
				+ "WHERE uc.userid=? AND uc.isCancel=0) coled ORDER BY coled.collectDate DESC";
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlExceptSelect, userid);
	}
	 

	public User getUserInfo(Integer id) {
		return User.me().findFirst("select A.*,B.headimgurl,B.nickname  from t_bus_user A left join t_bus_user_wx B on A.id=B.userid where B.id=?",id);
	}

	public List<Record> findMymess(Integer id) {
		return Db.find("select * from t_bus_user_mess");
	}

	public Page<UserMess> findMessByPage(int userid, int pageNum, int pageSize) {
		String sql = "from t_bus_user_mess where userid=? or type=2 order by createDate desc,id desc";
		return UserMess.me().paginate(pageNum, pageSize, "SELECT * ",sql,userid);
	}

	public UserMess findMessage(int id) {
		return UserMess.me().findFirst("select * from t_bus_user_mess where id=?",id);
	}

	public List<UserMess> findMess(Integer userid) {
		return UserMess.me().find("select * from t_bus_user_mess where userid=?",userid);
	}

	public Page<UserMoney> findMoneyChange(int pageNum, int pageSize, Integer userid) {
		String sql="from t_bus_user_money where userid=? order by createDate desc";
		
		return UserMoney.me().paginate(pageNum,pageSize,"select id,userid,cast(amount as char(11)) amount,`type`,`describe`,fromOrderNum,createDate,orderType ",sql,userid);
	} 
	/***
	 * 根据微信查询余额日志
	 * @param userid
	 * @return
	 */
    public List<UserMoney> findbalanceDetail(Integer id){
    	String sql="select A.*,B.title from t_bus_user_money A "
    			+"left join t_dict_store B on B.storeid=A.storeid "
    			+"left join t_bus_user_wx C on C.id=A.userid  where C.id=? order by createDate desc;";   	
    	return UserMoney.me().find(sql,id);    	
    }
    /**
     * 根据登录用户微信id查询余额
     * @param userid
     * @return
     */
    public double findMoney(Integer id){
    	return Db.queryDouble("select money from t_bus_user where id=(select userid  from t_bus_user_wx where id=?)",id);
    }
   
	public Recharge getRecharrge(String orderNum) {
		return Recharge.me().findFirst("select * from t_bus_user_recharge where chargeNum=?",orderNum);
	}

	public void cancelMess(int userid, String[] messids) {
		String sql = "delete from t_bus_user_mess wHERE userid=? AND id=?";
		Object[][] params = new Object[messids.length][2];
		for (int index = 0; index < messids.length; index++) {
			params[index][0] = userid;
			params[index][1] = messids[index];
		}
		Db.batch(sql, params, messids.length);
	}
	/**
	 * 获取用户的信息
	 * @param phone
	 * @return
	 */
	public User findByPhone(String phone) {
		return User.me().findFirst("select * from t_bus_user where phone =?",phone);
	}
	/**
	 * 获取导购的信息
	 * @param id
	 * @return
	 */
	public AdSeller findByAdSellerUid(String id) {
		return AdSeller.me().findFirst("select * from t_auth_admin_seller where id =?",id);
	}
	
	/**
	 * 获取导购的信息
	 * @param id
	 * @return
	 */
	public Record findInviteUserId(int id) {
		return Db.findFirst("select A.trueName,B.title from t_auth_admin_seller A left join t_dict_store B on A.storeid=B.storeid where A.id=?",id);
	}
	
	/**
	 * 首页展示商品
	 * 目前（畅销热品   titleCode='S00001'）标题
	 * @return
	 */
	public String findBestTitle(){		
		return Db.queryStr("select title from t_bus_goods_title where titleCode='S00001' ");		
	}
	/**
	 * 首页推送弹窗
	 * 入会礼	
	 * @return
	 */
	public HomeBag findFirstWindow(){	
		return HomeBag.me().findFirst("select * from t_bus_home_window where isPopup=1 and pushDateType=3 and isClose=0");
	}
	/**
	 * 首页推送弹窗
	 * 生日礼	
	 * @return
	 */
	public HomeBag findBirthdayWindow(){		
		return HomeBag.me().findFirst("select * from t_bus_home_window where isPopup=1 and pushDateType=1 and isClose=0");		
	}
	/**
	 * 首页推送弹窗
	 * 生产礼
	 * @return
	 */
	public HomeBag findProductionWindow(){		
		return HomeBag.me().findFirst("select * from t_bus_home_window where isPopup=1 and pushDateType=2 and isClose=0");		
	}
	/**
	 * 首页推送弹窗
	 * 立即发送
	 * @return
	 */
	public List<HomeBag> findNowSend(){
		return HomeBag.me().find("select * from t_bus_home_window where isPopup=1 and pushDateType=0 and isClose=0");		
	}
	/**
	 * 首页推送弹窗
	 * 定时发送
	 * @return
	 */
	public List<HomeBag> findDelaySend(String nowDate){		
		return HomeBag.me().find("select * from t_bus_home_window where isPopup=1 and pushDateType=4 and isClose=0 and pushDate='"+nowDate+"'");		
	}
	/**
	 * 查询礼包记录表中是否有数据   以验证该用户是否领取过
	 * 
	 * @return
	 */
	public Record findGiftLog(int giftid,int userid){
		return Db.findFirst("select * from t_bus_user_gift_log where userid=? and giftid=?",userid,giftid);		
	}
	/**
	 * 记录礼包领取日志
	 */
	public void giftlog(int userid, int giftid, int pushDateType){
		Db.update("INSERT INTO t_bus_user_gift_log(userid,giftid,pushDateType,createDate) VALUES(?,?,?,?)",userid,giftid,pushDateType,DatetimeKit.getFormatDate("yyyy-MM-dd HH:mm:ss"));
	}
	/**
	 * 添加到管家婆
	 * @param card
	 * @param aname
	 * @param aphone
	 * @param date
	 * @param storeNo
	 * @param ssx
	 * @return
	 */
	public int saveVip(String card,String aname,String aphone,String date,String storeNo,String ssx) {
		return Db.use("sqlserver").update("insert into VipCards (CardNo,HandOutDate,ValidDate,HolderName,HolderTel,MakeDate,ClearDate,HandOutDept,CardTypeID,sex) values(?,?,?,?,?,?,?,?,?,?)", card,date,"2099-12-31",aname,aphone,date,date,storeNo,1,ssx);
	}

	public Record findVipId(String aphone) {
		return Db.use("sqlserver").findFirst("select CardID from VipCards where HolderTel=?",aphone);
	}
	public int saveVipSum(Integer id, String time) {
		return Db.use("sqlserver").update("insert into VIPIntegralSummary (CardID,LastTime) values(?,?)",id,time);
	}
}

